if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_over_time_order_add')
          and type in ('P','PC'))
   drop procedure bd_proc_over_time_order_add
go


create  procedure bd_proc_over_time_order_add (@ot_date datetime,@ot_detail_dto_json varchar(max),@workshop_approver varchar(max),@labour_union_approver varchar(max),@order_id int,@order_no varchar(50),@ip varchar(200),@operator varchar(200),@reason varchar(max),@applyType varchar(10) = '2',@sourceKey int=1) as
BEGIN

-- 关闭打印受影响行数1
SET NOCOUNT ON;


-- 	--  异常扑捉机制
		BEGIN TRY

			--确定@sourceKey
			set @sourceKey = case when @sourceKey=2 then @sourceKey else 1 end;

			-- 该加班单的审批状态
			-- 0待审核 1审核中 2驳回 3通过 4自动审批
			declare @over_time_order_status int = 0;

			CREATE TABLE #over_time_detail(
				id INT IDENTITY,
				userSerial varchar(200) COLLATE Chinese_PRC_CI_AS,
				userNo varchar(200)	COLLATE Chinese_PRC_CI_AS,
				userName varchar(200)	COLLATE Chinese_PRC_CI_AS,
				deptSerial varchar(200) COLLATE Chinese_PRC_CI_AS,
				deptName varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shiftId varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shiftBh varchar(200)	COLLATE Chinese_PRC_CI_AS,
				otBegin int,
				otEnd int,
				reason varchar(max)	COLLATE Chinese_PRC_CI_AS,
				date date,
				computeMinute int,
				beginDateTime datetime,
				endDateTime datetime,
				status int, -- 加班单审批状态,
				job_grade varchar(max) COLLATE Chinese_PRC_CI_AS,
				technological_job varchar(max) COLLATE Chinese_PRC_CI_AS
			)

			--创建 返回不满足加班的人的原因
			create table #return_table(
				user_serial VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200)	COLLATE Chinese_PRC_CI_AS
			)

		-- 开启事务
			BEGIN TRAN

        insert into #over_time_detail(userSerial,userNo,userName,deptSerial,deptName,shiftBh,otBegin,otEnd,reason)
        select * from openjson(@ot_detail_dto_json)
        WITH (
          userSerial varchar(200),
          userNo varchar(200),
          userName varchar(200),
          deptSerial varchar(200),
          deptName varchar(200),
					shiftId varchar(200),
          otBegin int,
          otEnd int,
          reason varchar(max)
        );


				-- 更新数据 时长 日期 审核状态 开始时间 结束时间
				update #over_time_detail set status = 0, date = @ot_date, computeMinute = (otEnd - otBegin),beginDateTime = dateadd(minute,otBegin,@ot_date), endDateTime = dateadd(minute,otEnd,@ot_date);


				-- 更新数据 班次ID
				update a set a.shiftId = b.id
				from #over_time_detail a
				inner join zt_banci b on a.shiftBh = b.bh;

				-- 验证数据有效性
				--更新人员岗位信息
				update d
				set d.job_grade = u.job_grade,
						d.technological_job = u.technological_job
				from #over_time_detail d
				INNER JOIN dt_user u on u.user_serial = d.userSerial;

				--1员工属性【行政岗位级别】副科长及以上，【技术岗位级别】：G2、G3、G4、GF2、GF3、GF4不能加班申请
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct b.userSerial,b.userNo,b.userName,b.deptName,'岗位级别限制，无法申请加班单'
				from #over_time_detail b where b.userSerial in(
				select x.userSerial from (
					select u.userSerial,
								u.job_grade,
								u.technological_job,
								p.overtime_reject_administrative,
								p.overtime_reject_technology
					from #over_time_detail u ,bd_sys_global_params p
					)x
					where x.overtime_reject_administrative like '%' + case when x.job_grade is null or x.job_grade='' then 'N/A' else x.job_grade end +'%' or
								x.overtime_reject_technology like  '%' + case when x.technological_job is null or x.technological_job='' then 'N/A' else x.technological_job end  +'%'
				)


				-- 删除不满足的数据
				delete a
				from #over_time_detail a
				inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial COLLATE Chinese_PRC_CI_AS


				--2加班时间重叠
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct b.userSerial,b.userNo,b.userName,b.deptName,
				'该时间段存在加班记录,冲突时间段：'+case when a.kssj>b.beginDateTime then convert(varchar(16),a.kssj,120) else convert(varchar(16),b.beginDateTime,120) end +' - '+
				case when a.jssj<b.endDateTime then convert(varchar(16),a.jssj,120) else convert(varchar(16),b.endDateTime,120) end
				from lr_jiaba a
				inner join #over_time_detail b on a.user_serial=b.userSerial
				inner join dt_dep c on b.deptSerial = c.dep_serial
				inner join dt_user d on b.deptSerial = d.user_dep
				INNER JOIN bd_over_time_order o on a.order_id = o.order_id
				where isnull(o.is_delete,0) = 0 and  isnull(a.is_delete,0) = 0 and not(a.kssj >= b.endDateTime or a.jssj <= b.beginDateTime)
						and o.status !=2
				-- 删除不满足的数据
				delete a
				from #over_time_detail a
				inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial COLLATE Chinese_PRC_CI_AS


        -- 3检测是否为封账状态
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,' 该时段考勤已经封账,不能加班'
        from #over_time_detail a
        left join bd_kq_accounts c on a.userSerial = c.user_serial  and year(a.date) = c.year and month(a.date) = c.month
				where isnull(c.lock_status,0)>0
				-- 删除不满足的数据
				delete a
				from #over_time_detail a
				inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial


				declare @user_count int = 0;
				declare @compute_result int = 0;


				-- 员工个数（去重过）
				select @user_count = isnull((select count(0) from (select distinct userSerial from #over_time_detail) as a),0)

				-- 共计时长
				select @compute_result = isnull(sum(datediff(minute, beginDateTime,endDateTime)),0) from #over_time_detail


				IF isnull(@user_count,0) > 0 BEGIN


					insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,jbrq,order_id,ot_begin,ot_end,over_time_type_id,is_repast,shift_id,is_delete,settle_year,settle_month)
					select userSerial,0,beginDateTime as kssj,endDateTime as jssj,computeMinute as jsjg,reason as bz,@operator as gly_no,date as jbrq,@order_id,
					case when otBegin<0 then -1440-otBegin else otBegin end ,
					case when otEnd<0 then -1440-otEnd else otEnd end ,
					0,0,isnull(a.shiftId,0),0,DATEPART(year, dbo.bd_fn_getSettleDate(date)),DATEPART(month, dbo.bd_fn_getSettleDate(date))
					from #over_time_detail a


					if (@applyType is null or @applyType!='1')
					BEGIN
						set @applyType = '2'
					END

					insert into bd_over_time_order(order_id,order_no,ot_date,sum_user,sum_minute,is_leave,status,create_user,create_time,workshop_approver,labour_union_approver,is_delete,reason,apply_type,source_key)
					values(@order_id,@order_no,@ot_date,@user_count,@compute_result,null,@over_time_order_status,@operator,GETDATE(),@workshop_approver,@labour_union_approver,0,null,@applyType,@sourceKey)

				END

				--单据保存之后，若本次加班单时通过排班生成的加班单（@sourceKey=2），则需要将明细中的信息关联排班明细表，更新is_created_ot的值为1;
				--并将bd_temp_schedule_ot 中对应的记录删除。
				if @sourceKey=2
					BEGIN
						--1.更新排班明细表状态
						update d
							set is_created_ot = 1
						from bd_kq_schedule_detail d
						INNER JOIN #over_time_detail o on o.userSerial = convert(varchar(50),d.user_serial) and o.date = d.shift_date
						--2.删除数据
						delete  o
						from bd_temp_schedule_ot o
						INNER JOIN #over_time_detail d on d.userSerial = convert(varchar(50),o.user_serial) and o.ot_date = d.date
						where o.creator_id = @operator
					END

				--返回数据
				select * from #return_table order by user_serial

-- 				---考勤重新计算
-- 			  declare @user_serial_str varchar(max) = '';
--         select @user_serial_str += iif(isnull(userSerial,'') = '','',concat(',',userSerial)) from #over_time_detail
--
--         -- 去除最前面的逗号
--         set @user_serial_str = iif(left(@user_serial_str,1) = ',',right(@user_serial_str,len(@user_serial_str)-1),@user_serial_str)
--
--         --计算考勤(3代表加班调用)先注释掉
--         EXEC bd_proc_timecard @ot_date, @user_serial_str, 3

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH

--  清除临时表
	drop table #over_time_detail;
	drop table #return_table

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
go